<?php
class hrd_absen_model extends MY_Model {
	var $nama_tabel = 'hrd_absen';
	
	function GetJSON($idJadwal, $idKaryawan) {
		$ls_sql	= "	SELECT	a.id_absen_pk, a.telat, a.id_status_absen_fk, a.id_jadwal_fk, a.jam_masuk, a.jam_keluar, TO_CHAR(a.tgl_masuk,'DD/MM/YYYY') tgl_masuk, TO_CHAR(a.tgl_keluar,'DD/MM/YYYY') tgl_keluar, a.urutan, a.hari, a.cepat_pulang, a.potongan
					FROM	".$this->nama_tabel." a 
					WHERE id_jadwal_fk = '".$idJadwal."'
					AND id_karyawan_fk = '".$idKaryawan."'
					ORDER BY ".(post('sort')=='' ? 'urutan' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));
					
					
		$row = $this->db->query($ls_sql)->result_array();
		$data = array('rows' => $row, 
					  'total' => count($row),
					  'page' => 1);
		
		return json_encode($data);
		// print_r($ls_sql);
	}

	function GetImportJSON()
	{
		$ls_sql	= "	SELECT	a.hari, b.nama_karyawan, TO_CHAR(a.tgl_masuk,'DD/MM/YYYY') tgl_masuk, TO_CHAR(a.tgl_keluar,'DD/MM/YYYY') tgl_keluar, a.jam_masuk, a.jam_keluar, a.telat, c.nama_status_absen, a.cepat_pulang, a.potongan
					FROM	hrd_absen a 
					LEFT JOIN hrd_karyawan b ON a.id_karyawan_fk = b.id_karyawan_pk 
					LEFT JOIN hrd_status_absen c ON a.id_status_absen_fk = c.id_status_absen_pk 
					WHERE id_status_absen_fk is not null
					AND tgl_masuk BETWEEN 
							TO_DATE('".date_format3(post('date_from'), 'YYYY-MM-DD')."','YYYY-MM-DD') AND 
							TO_DATE('".date_format3(post('date_to'), 'YYYY-MM-DD')."','YYYY-MM-DD') AND
							(
								UPPER(b.nama_karyawan) LIKE '%".strtoupper(post('keyword'))."%' 
								OR UPPER(c.nama_status_absen) LIKE '%".strtoupper(post('keyword'))."%'
							)
					ORDER BY ".(post('sort')=='' ? 'tgl_masuk' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));
					
					// print_r($ls_sql);
		return json_encode( $this->paging($ls_sql) );
	}

	function Add($arr_data){
		$this->db->insert($this->nama_tabel, $arr_data);
	}

	function Update($arr_data, $arr_rule){
		$this->db->update($this->nama_tabel, $arr_data, $arr_rule);
	}

	function DeleteSingle($arr_rule) {
		try {
			$this->db->delete($this->nama_tabel, array('id_absen_pk' => $arr_rule['id_absen_pk']));
			return '';
		} catch(Exception $e) {
			return $e->getMessage();
		}
	}

	function getIdJadwal($periode, $grubAbsen) {
		$this->db->select('id_jadwal_pk');
		$this->db->where('id_periode_fk', $periode);
		$this->db->where('id_grub_absen_fk', $grubAbsen);

		return json_encode($this->db->get('hrd_jadwal')->row_array());
	}

	function getPeriode($periode) {
		$sql = "SELECT TO_CHAR(tgl_mulai, 'DD/MM/YYYY') tgl_mulai, TO_CHAR(tgl_selesai, 'DD/MM/YYYY') tgl_selesai
				FROM HRD_PERIODE 
				WHERE ID_PERIODE_PK = '".$periode."'
				";

		$data = $this->db->query($sql)->row_array();
		return json_encode($data);
	}

	function laporan() {
		$ls_sql = "SELECT a.id_karyawan_fk, b.nama_karyawan,
	                count(decode(id_status_absen_fk, 'MANGKIR', id_status_absen_fk)) mangkir,
	                count(decode(id_status_absen_fk, 'LIBUR', id_status_absen_fk)) libur,
	                count(decode(id_status_absen_fk, 'HADIR', id_status_absen_fk)) hadir,
	                count(decode(id_status_absen_fk, 'IJIN', id_status_absen_fk)) ijin,
	                count(decode(id_status_absen_fk, 'SAKIT', id_status_absen_fk)) sakit,
	                count(decode(id_status_absen_fk, 'DINAS', id_status_absen_fk)) dinas,
	                count(decode(id_status_absen_fk, 'CUTI', id_status_absen_fk)) cuti,
	                sum(telat) telat,
	                sum(cepat_pulang) cepat_pulang,
	                sum(potongan) potongan
	                ,(
				        SELECT SUM(nilai)
				        FROM hrd_lembur c
				        WHERE C.ID_KARYAWAN_FK = a.id_karyawan_fk
				        AND c.tgl_lembur >= to_date('".post('tgl_awal')."', 'dd/mm/yyyy')
				        AND c.tgl_lembur <= to_date('".post('tgl_akhir')."', 'dd/mm/yyyy')
				    ) AS nilai
				    ,(
				        SELECT SUM(lama_jam_lembur)
				        FROM hrd_lembur c
				        WHERE C.ID_KARYAWAN_FK = a.id_karyawan_fk
				        AND c.tgl_lembur >= to_date('".post('tgl_awal')."', 'dd/mm/yyyy')
				        AND c.tgl_lembur <= to_date('".post('tgl_akhir')."', 'dd/mm/yyyy')
				    ) AS lama_jam_lembur
					,(
				        SELECT SUM(uang_makan)
				        FROM hrd_lembur c
				        WHERE C.ID_KARYAWAN_FK = a.id_karyawan_fk
				        AND c.tgl_lembur >= to_date('".post('tgl_awal')."', 'dd/mm/yyyy')
				        AND c.tgl_lembur <= to_date('".post('tgl_akhir')."', 'dd/mm/yyyy')
				    ) AS uang_makan
					FROM HRD_ABSEN a
					RIGHT JOIN hrd_karyawan b ON a.id_karyawan_fk=b.id_karyawan_pk
					WHERE a.tgl_masuk >= to_date('".post('tgl_awal')."', 'dd/mm/yyyy')
					AND a.tgl_masuk <= to_date('".post('tgl_akhir')."', 'dd/mm/yyyy')
					AND a.id_karyawan_fk like '%".post('id_karyawan_pk')."%'
					AND UPPER(NVL(b.id_cost_center_fk,' ')) like '%".post('id_cost_center_fk')."%'
					GROUP BY a.id_karyawan_fk, b.nama_karyawan
					ORDER BY ".(post('sort')=='' ? 'a.id_karyawan_fk' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));
		/*$ls_sql = "select  laporan.*
				   from (
				   	".$ls_sql."
				   ) laporan
				  ";*/
		
		// return json_encode( $this->hrd_paging($ls_sql) );
		$list = $this->db->query($ls_sql)->result_array();
		return json_encode( $list );
	}

	function hrd_paging($ls_sql){
		// Explode SQL Statement into array [SELECT, FROM, WHERE, ORDER BY]
		$tmp = ''; 
		$itter_after_reset	= 0;
		$itter_bracket		= 0;
		$keyword_position	= '';
		$arr_word			= array();
		$arr_keyword_query	= array('SELECT' => '', 'FROM' => '', 'WHERE' => '', 'ORDER BY' => '');
		$st_cutword			= false;
		for($i=0,$max_i=strlen($ls_sql); $i<=$max_i; $i++){
			$char = ($i==$max_i ? ' ' : substr($ls_sql, $i, 1));
			if($char == '('){ $itter_bracket++; }else if($char==')'){ $itter_bracket--; }
			
			if(in_array($char, array(' ','	',chr(10),chr(13)))){
				if($itter_after_reset == 0){ $word = $tmp; $arr_word = array_merge($arr_word, array($word)); }
				$itter_after_reset++; $tmp = '';
				$st_cutword = true;
			}else{
				$word = '';
				$tmp .= $char;
				$itter_after_reset = 0;
				$st_cutword = false;
			}
			
			
			if(strtoupper($word)=='SELECT' && $itter_bracket==0){ $keyword_position = 'SELECT'; }
			else if(strtoupper($word)=='FROM' && $itter_bracket==0){ $keyword_position = 'FROM'; }
			else if(strtoupper($word)=='WHERE' && $itter_bracket==0){ $keyword_position = 'WHERE'; }
			else if(strtoupper($word)=='BY' && $arr_word[count($arr_word)-2] == 'ORDER' && $itter_bracket==0){ $keyword_position = 'ORDER BY'; }
			
			
			if($keyword_position=='SELECT'){
				$arr_keyword_query['SELECT'] .= (($st_cutword && $itter_after_reset<=1) || !$st_cutword ? $char : '');
			}else if($keyword_position=='FROM'){
				$arr_keyword_query['FROM'] .= (($st_cutword && $itter_after_reset<=1) || !$st_cutword ? $char : '');
			}else if($keyword_position=='WHERE'){
				$arr_keyword_query['WHERE'] .= (($st_cutword && $itter_after_reset<=1) || !$st_cutword ? $char : '');
			}else if($keyword_position=='ORDER BY'){
				$arr_keyword_query['ORDER BY'] .= (($st_cutword && $itter_after_reset<=1) || !$st_cutword ? $char : '');
			}
		}
		$arr_keyword_query['SELECT'] = trim($arr_keyword_query['SELECT']);
		$arr_keyword_query['FROM'] = trim($arr_keyword_query['FROM']);
		$arr_keyword_query['WHERE'] = trim($arr_keyword_query['WHERE']);
		$arr_keyword_query['ORDER BY'] = trim($arr_keyword_query['ORDER BY']);
		
		$arr_keyword_query['SELECT'] = trim(substr($arr_keyword_query['SELECT'],0,strlen($arr_keyword_query['SELECT'])-4));
		if($arr_keyword_query['WHERE'] != ''){
			$arr_keyword_query['FROM'] = trim(substr($arr_keyword_query['FROM'],0,strlen($arr_keyword_query['FROM'])-5));
		}
		if($arr_keyword_query['WHERE'] != '' && $arr_keyword_query['ORDER BY'] != ''){
			$arr_keyword_query['WHERE'] = trim(substr($arr_keyword_query['WHERE'],0,strlen($arr_keyword_query['WHERE'])-8));
		}
		
		
		// Get Necessary Variable For Paging
		$perpage	= intval(post('rows')) > 0 ? intval(post('rows')) : $this->web_option("DATA_PER_PAGE");
		$total = $this->get_data("	SELECT COUNT(1) 
									FROM ".$arr_keyword_query['FROM']." ".
									($arr_keyword_query['WHERE']!="" ? "WHERE ".$arr_keyword_query['WHERE'] : ""));
		$cur_page = ((post('page') > ceil($total/$perpage)) ? ceil($total/$perpage) : (post('page') < 1 ? 1 : post('page')));
		if($cur_page < 1) $cur_page = 1;
		
		
		// Set Start & End Rows
		$ln_start	= (($cur_page - 1) * $perpage) + 1;
		$ln_end		= $ln_start + $perpage - 1;
		
		// Patching For oracle
		$arr_keyword_query['SELECT']	= (trim($arr_keyword_query['SELECT']) == '*' ? $arr_keyword_query['FROM'].'.' : '') . $arr_keyword_query['SELECT'];
		
		// Create New SQL with Paging
		$ls_sql_with_paging	= "	SELECT * 
								FROM (
										SELECT ".$arr_keyword_query['SELECT'].", ROW_NUMBER() OVER (ORDER BY ".$arr_keyword_query['ORDER BY'].") AS xrownum
										FROM ".$arr_keyword_query['FROM']." 
										".($arr_keyword_query['WHERE']!="" ? "WHERE ".$arr_keyword_query['WHERE'] : "").") tbl
								WHERE	xrownum BETWEEN ".$ln_start." AND ".$ln_end."
								ORDER BY xrownum";
		
		$result	= $this->db->query($ls_sql_with_paging);
		$arr_data = $result->result_array();
		$result->free_result();			

		// Hitung jumlah
		$ls_sql_count	= "	SELECT count(1) as total
								FROM (
										SELECT ".$arr_keyword_query['SELECT'].", ROW_NUMBER() OVER (ORDER BY ".$arr_keyword_query['ORDER BY'].") AS xrownum
										FROM ".$arr_keyword_query['FROM']." 
										".($arr_keyword_query['WHERE']!="" ? "WHERE ".$arr_keyword_query['WHERE'] : "").") tbl
								WHERE	xrownum BETWEEN ".$ln_start." AND ".$ln_end."
								ORDER BY xrownum"; 
		$hitungTotal = $this->db->query($ls_sql_count)->row_array();
		$total = $hitungTotal['total'];
		// print_r($hitungTotal);
		
		
		// Create Array Response For Datagrid
		$arr_response = array(	'rows'=> $arr_data, 
								'total' => $total, 
								'page'=>$cur_page,
								//'next'=>($total==0 || post('page')==(ceil($total/$perpage)) ? 0 : post('page')+1), 
								'sessid'=> _SESSID);
								
		return $arr_response;
	}

}
?>